package cn.edu.sdjzu.xg2106.dao;

import cn.edu.sdjzu.xg2106.domain.Department;
import cn.edu.sdjzu.xg2106.domain.Teacher;
import cn.edu.sdjzu.xg2106.domain.University;
import cn.edu.sdjzu.xg2106.exception.BysjException;
import cn.edu.sdjzu.xg2106.util.Condition;
import cn.edu.sdjzu.xg2106.util.JdbcHelper;
import cn.edu.sdjzu.xg2106.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class TeacherDao {
    private TeacherDao(){}
    public static TeacherDao teacherDao = null;
    public static TeacherDao getInstance(){
        if (teacherDao==null){
            teacherDao=new TeacherDao();
        }
        return teacherDao;
    }
    public void delete(int id) throws SQLException{
        Connection connection = JdbcHelper.getConn();
        PreparedStatement preparedStatement =
                connection.prepareStatement("delete from teacher"+" where id =?");
        preparedStatement.setInt(1,id);
        preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement,connection);
    }
    public void add(Teacher teacher, Connection connection)throws SQLException, BysjException {
        String sql_insert = "insert into Teacher (id,name,department_id) values(?,?,?)";
        PreparedStatement pstmt_insert = connection.prepareStatement(sql_insert);
        pstmt_insert.setInt(1,teacher.getId());
        pstmt_insert.setString(2,teacher.getName());
        pstmt_insert.setInt(3,teacher.getDepartment_id());
        pstmt_insert.executeUpdate();
        JdbcHelper.close(pstmt_insert,connection);
    }
    public void update(Teacher teacher, Connection connection)throws SQLException{
        String  sql_update = "update teacher set name =?,department_id =? WHERE id =?";
        PreparedStatement pstmt_update = connection.prepareStatement(sql_update);
        pstmt_update.setInt(3,teacher.getId());
        pstmt_update.setString(1,teacher.getName());
        pstmt_update.setInt(2,teacher.getDepartment_id());
        pstmt_update.executeUpdate();
        JdbcHelper.close(pstmt_update,connection);

    }
    public Teacher find(int id) throws SQLException {
        Teacher desiredTeacher = null;
        Connection connection= JdbcHelper.getConn();
        String sql_select_id="select * from teacher where id=?";
        PreparedStatement pstmt_select_id=connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id=pstmt_select_id.executeQuery();
        if (resultSet_select_id.next()){
            String name = resultSet_select_id.getString("name");
            int department_id = resultSet_select_id.getInt("department_id");
            desiredTeacher = new Teacher(id, name, department_id);
        }
        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);
        return desiredTeacher;
    }
    public Collection<Teacher> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        //创建集合类对象
        Collection<Teacher> desiredTeacher = new HashSet<Teacher>();
        //创建查询教师的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM teacher");
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection, "teacher", conditionList);
        if (pagination != null) {
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }
        PreparedStatement pstmt_select_all = (connection).prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt_select_all.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int department_id = resultSet.getInt("department_id");
            Teacher desiredTeachers = new Teacher(id, name, department_id);
            desiredTeacher.add(desiredTeachers);
        }
        JdbcHelper.close(resultSet, pstmt_select_all);
        return desiredTeacher;
    }
    public int countAll(Department departmentToDelete) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String sql_count_by_department_id = "SELECT COUNT(id) as cnt_by_department_id FROM teacher WHERE department_id = ?";
        PreparedStatement pstmt_count_by_department_id =
                connection.prepareStatement(sql_count_by_department_id);
        pstmt_count_by_department_id.setInt(1, (int) departmentToDelete.getId());
        int count_by_department_id = 0;
        ResultSet resultSet_count_by_department_id = pstmt_count_by_department_id.executeQuery();
        if (resultSet_count_by_department_id.next()){
            count_by_department_id = resultSet_count_by_department_id.getInt("cnt_by_department_id");
        }
        JdbcHelper.close(resultSet_count_by_department_id, pstmt_count_by_department_id, connection);
        return count_by_department_id;
    }
}
